package xml.resource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.restlet.data.Status;
import org.restlet.representation.Representation;
import org.restlet.resource.Get;
import org.restlet.resource.Post;
import org.restlet.resource.ResourceException;
import xml.converter.JDOMElementRepresentationConverter;
import xml.form.Form;
import xml.model.Adresse;
import xml.model.Contact;
import xml.model.Email;
import xml.model.Groupe;
import xml.model.Telephone;
import xml.model.Type;
import xml.validator.LengthValidator;
import xml.validator.LongRangeValidator;
import xml.validator.NotEmptyValidator;
import xml.validator.RegexValidator;

/**
 * Resource représentant les contacts d'un groupe
 * @author marya
 */
public class ContactsResource extends AbstractResource {

    private Groupe groupe;

    public ContactsResource() {
        super();
    }

    @Override
    protected void doInit() {
        Long idGroupe;
        final String sqlGroupe = " SELECT * FROM groupe WHERE url IS NULL AND id_groupe= ";
        final String sqlContact = "SELECT contact.id_contact, contact.nom, contact.prenom, "
                + "contact.voie, contact.ville, "
                + "contact.code_postal, contact.pays "
                + "FROM contact_groupe "
                + "INNER JOIN groupe ON "
                + "contact_groupe.id_groupe = groupe.id_groupe AND groupe.url IS NULL "
                + "INNER JOIN contact ON "
                + "contact.id_contact = contact_groupe.id_contact "
                + "WHERE contact_groupe.id_groupe = ";


        final String sqlTel = "SELECT tel.value, type.libelle FROM tel "
                + "INNER JOIN type ON type.id_type = tel.id_type "
                + "WHERE tel.id_contact = ";
        final String sqlEmail = "SELECT email.value, type.libelle FROM email "
                + "INNER JOIN type ON type.id_type = email.id_type "
                + "WHERE email.id_contact = ";

        String idGroupeAttribute = getRequest().getAttributes().get("idGroupe").toString();
        LongRangeValidator longRangeValidator = new LongRangeValidator(1l, null);


        //Validation de l'id de la promotion
        if (longRangeValidator.isValid(idGroupeAttribute)) {
            idGroupe = Long.parseLong(idGroupeAttribute);
        } else {
            errors.add(new ResourceError("invalidGroupe"));
            throw new ResourceException(Status.CLIENT_ERROR_BAD_REQUEST);
        }

        Connection connexion = null;
        try {
            connexion = database.getNewConnection();
            ResultSet rs = connexion.createStatement().executeQuery(sqlGroupe + idGroupe);
            if (!rs.next()) {
                setExisting(false);
            } else {
                groupe = new Groupe(rs.getInt("id_groupe"), rs.getString("nom"), new ArrayList<Contact>());

                ResultSet rsContact = connexion.createStatement().executeQuery(sqlContact + idGroupe);
                ResultSet rsTel = null;
                ResultSet rsEmail = null;

                while (rsContact.next()) {
                    int idContact = rsContact.getInt("id_contact");
                    Contact contact = new Contact(
                            rsContact.getString("nom"),
                            rsContact.getString("prenom"),
                            new Adresse(rsContact.getString("voie"),
                            rsContact.getString("ville"),
                            rsContact.getString("code_postal"),
                            rsContact.getString("pays")));
                    rsTel = connexion.createStatement().executeQuery(sqlTel + idContact);
                    while (rsTel.next()) {
                        contact.getTels().add(new Telephone(Type.getType(rsTel.getString("libelle")), rsTel.getString("value")));
                    }
                    rsEmail = connexion.createStatement().executeQuery(sqlEmail + idContact);
                    while (rsEmail.next()) {
                        contact.getEmails().add(new Email(Type.getType(rsEmail.getString("libelle")), rsEmail.getString("value")));
                    }
                    contact.setUri(getRootRef().getHostIdentifier() + "/contacts/" + idContact);
                    groupe.getContacts().add(contact);
                }

                groupe.setUri(getRootRef().getHostIdentifier() + "/groupes/" + idGroupe);
            }
            connexion.close();
        } catch (SQLException e) {
            DbUtils.closeQuietly(connexion);
            throw new ResourceException(Status.SERVER_ERROR_INTERNAL, e);
        }
    }

    @Get
    public Representation doGet() throws Exception {
        return (new JDOMElementRepresentationConverter()).convert(groupe.toJDOMElement());
    }

    @Post
    public Representation doPost(Representation entity) throws Exception {
        Representation response = null;
        Connection connexion = null;
        final String sqlContact = "INSERT INTO contact (nom,prenom,voie,ville,code_postal,pays) VALUES (?,?,?,?,?,?)";
        final String sqlContactToGroupe = "INSERT INTO contact_groupe(id_contact,id_groupe) VALUES (?,?)";
        final String sqlTel = "INSERT INTO tel(value,id_contact,id_type) VALUES (?,?,?)";
        final String sqlEmail = "INSERT INTO email(value,id_contact,id_type) VALUES (?,?,?)";
        Form form = getContactForm(entity);
        if (!form.isValid()) {
            for (String error : form.getMessages()) {
                errors.add(new ResourceError(error));
                setStatus(Status.CLIENT_ERROR_BAD_REQUEST);
            }
        } else {
            try {
                connexion = database.getNewConnection();
                connexion.setAutoCommit(false);
                QueryRunner queryRunner = new QueryRunner();
                PreparedStatement preparedStatement = connexion.prepareStatement(sqlContact, PreparedStatement.RETURN_GENERATED_KEYS);
                queryRunner.fillStatement(
                        preparedStatement,
                        form.getFirstValue("nom"),
                        form.getFirstValue("prenom"),
                        form.getFirstValue("voie"),
                        form.getFirstValue("ville"),
                        form.getFirstValue("codePostal"),
                        form.getFirstValue("pays"));
                preparedStatement.executeUpdate();
                ResultSet rs = preparedStatement.getGeneratedKeys();
                rs.next();
                Integer idContact = rs.getInt(1);
                Integer result = queryRunner.update(connexion, sqlContactToGroupe, idContact, groupe.getIdGroupe());

                String telHome = form.getFirstValue("telHome");
                Integer iTelHome = 1;

                String telPrivate = form.getFirstValue("telPrivate");
                Integer iTelPrivate = 1;

                String telWork = form.getFirstValue("telWork");
                Integer iTelWork = 1;

                String emailHome = form.getFirstValue("emailHome");
                Integer iEmailHome = 1;

                String emailPrivate = form.getFirstValue("emailPrivate");
                Integer iEmailPrivate = 1;

                String emailWork = form.getFirstValue("emailWork");
                Integer iEmailWork = 1;



                if (telHome != null) {
                    iTelHome = queryRunner.update(connexion, sqlTel, telHome, idContact, 1);
                }

                if (telPrivate != null) {
                    iTelPrivate = queryRunner.update(connexion, sqlTel, telPrivate, idContact, 2);
                }

                if (telWork != null) {
                    iTelWork = queryRunner.update(connexion, sqlTel, telWork, idContact, 3);
                }


                if (emailHome != null) {
                    iEmailHome = queryRunner.update(connexion, sqlEmail, emailHome, idContact, 1);
                }

                if (emailPrivate != null) {
                    iEmailPrivate = queryRunner.update(connexion, sqlEmail, emailPrivate, idContact, 2);
                }

                if (emailWork != null) {
                    iEmailWork = queryRunner.update(connexion, sqlEmail, emailWork, idContact, 3);
                }


                if (idContact != null && idContact > 1
                        && result != null
                        && iTelHome != null && iTelHome > 0
                        && iTelPrivate != null && iTelPrivate > 0
                        && iTelWork != null && iTelWork > 0
                        && iEmailHome != null && iEmailHome > 0
                        && iEmailPrivate != null && iEmailPrivate > 0
                        && iEmailWork != null && iEmailWork > 0) {
                    connexion.commit();
                    Contact contact = new Contact(idContact);
                    contact.setUri(getRootRef().getHostIdentifier() + "/contacts/" + idContact);
                    response = contact.toXML();
                    setStatus(Status.SUCCESS_CREATED);
                } else {
                    DbUtils.rollbackAndCloseQuietly(connexion);
                    setStatus(Status.SERVER_ERROR_INTERNAL, "Impossible to create contact");
                }
            } catch (SQLException e) {
                DbUtils.rollbackAndCloseQuietly(connexion);
                setStatus(Status.SERVER_ERROR_INTERNAL, "Impossible to create contact");
                throw e;
            }
        }

        if (!errors.isEmpty()) {
            response = ResourceError.getListRepresentation(errors);
        }
        return response;

    }

    public Form getContactForm(Representation entity) {
        Form form = new Form(entity);
        NotEmptyValidator notEmptyValidator = new NotEmptyValidator();
        LengthValidator lengthValidator = new LengthValidator(255);
        LengthValidator shortLengthValidator = new LengthValidator(13);
        RegexValidator namesValidator = new RegexValidator("^([a-zA-Z'àâéèêëîïôùûçÀÂÉÈÔÙÛÇ\\s-]{1,100})$");
        RegexValidator telValidator = new RegexValidator("^0[1-6]{1}(([0-9]{2}){4})|((\\s[0-9]{2}){4})|((-[0-9]{2}){4})$");
        RegexValidator emailValidator = new RegexValidator("^[a-zA-Z0-9_.-]+@[a-zA-Z0-9-]+.[a-zA-Z0-9-.]+$");

        form.addValidators("nom", notEmptyValidator, lengthValidator, namesValidator);
        form.addValidators("prenom", notEmptyValidator, lengthValidator, namesValidator);

        if (form.getFirstValue("voie") != null) {
            form.addValidators("voie", notEmptyValidator, lengthValidator);
        }

        if (form.getFirstValue("ville") != null) {
            form.addValidators("ville", notEmptyValidator, lengthValidator);
        }

        if (form.getFirstValue("codePostal") != null) {
            form.addValidators("codePostal", notEmptyValidator, new LengthValidator(5));
        }

        if (form.getFirstValue("pays") != null) {
            form.addValidators("pays", notEmptyValidator, lengthValidator);
        }

        if (form.getFirstValue("emailWork") != null) {
            form.addValidators("emailWork", notEmptyValidator, lengthValidator, emailValidator);
        }

        if (form.getFirstValue("emailHome") != null) {
            form.addValidators("emailHome", notEmptyValidator, lengthValidator, emailValidator);
        }

        if (form.getFirstValue("emailPrivate") != null) {
            form.addValidators("emailPrivate", notEmptyValidator, lengthValidator, emailValidator);
        }

        if (form.getFirstValue("telWork") != null) {
            form.addValidators("telWork", notEmptyValidator, shortLengthValidator, telValidator);
        }

        if (form.getFirstValue("telHome") != null) {
            form.addValidators("telHome", notEmptyValidator, shortLengthValidator, telValidator);
        }

        if (form.getFirstValue("telPrivate") != null) {
            form.addValidators("telPrivate", notEmptyValidator, shortLengthValidator, telValidator);
        }



        return form;
    }
}
